from datetime import datetime
from typing import Union, Optional

from sqlalchemy import update, CursorResult, text, func, insert, delete
from sqlalchemy.ext.asyncio import AsyncSession
from sqlmodel import select, desc, col, asc, Session
from sqlmodel.sql.expression import SelectOfScalar, Select, and_

from src.base.beans import PageResult
from src.base.constants import id_generator
from src.domain.sys_tenant import SysTenant, SysTenantReq


async def get_tenant_by_id(dbSession: AsyncSession, id: int) -> SysTenant:
    result = await dbSession.execute(
        select(SysTenant).where(SysTenant.tenant_id == id).where(SysTenant.del_flag == False))
    return result.scalar_one_or_none()


def where_handle(stmt: Union[Select, SelectOfScalar], entity: SysTenantReq):
    stmt = stmt.where(SysTenant.del_flag == False)
    if entity:
        if entity.tenant_id:
            stmt = stmt.where(SysTenant.tenant_id == entity.tenant_id)
        if entity.tenant_name:
            stmt = stmt.where(col(SysTenant.tenant_name).like(f"%{entity.tenant_name}%"))
        if entity.org_name:
            stmt = stmt.where(col(SysTenant.tenant_name).like(f"%{entity.org_name}%"))
        if entity.org_credit_code:
            stmt = stmt.where(col(SysTenant.tenant_name).like(f"%{entity.org_credit_code}%"))
        if entity.status is not None:
            stmt = stmt.where(SysTenant.status == entity.status)
        if entity.create_time_range:
            start_time = datetime.strptime(entity.create_time_range[0], "%Y-%m-%d").date()
            end_time = datetime.strptime(entity.create_time_range[1], "%Y-%m-%d").date()
            stmt = stmt.where(
                and_(func.date(SysTenant.create_time) >= start_time, func.date(SysTenant.create_time) <= end_time))
    return stmt


async def get_tenant_list_by_condition(dbSession: AsyncSession, entity: Optional[SysTenantReq] = None) -> list[
    SysTenant]:
    if entity is None:
        entity = SysTenant()
    stmt = select(SysTenant)
    stmt = where_handle(stmt, entity)
    stmt = stmt.order_by(desc(SysTenant.sort))
    users = await dbSession.execute(stmt)
    return list(users.scalars())


async def get_tenant_page_by_condition(dbSession: AsyncSession, entity: Optional[SysTenantReq] = None) -> PageResult:
    if entity is None:
        entity = SysTenant()
    list_stmt = select(SysTenant)
    list_stmt = where_handle(list_stmt, entity)
    if entity.sorter:
        list_stmt = list_stmt.order_by(text(entity.sorter))
    else:
        list_stmt = list_stmt.order_by(asc(SysTenant.sort))
    list_stmt = list_stmt.limit(entity.pageSize).offset(entity.pageSize * (entity.current - 1))
    data = await dbSession.execute(list_stmt)
    data = list(data.scalars())

    total_stmt = select(func.count(SysTenant.tenant_id))
    total_stmt = where_handle(total_stmt, entity)
    total_result = await dbSession.execute(total_stmt)
    total = total_result.scalar_one_or_none()
    return PageResult(total=total, data=data, current=entity.current, pageSize=entity.pageSize)


async def insert_tenant(dbSession: AsyncSession, entity: SysTenantReq):
    entity.tenant_id = next(id_generator)
    entity.creator = entity.creator if entity.creator else 1
    insert_data = SysTenant(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    result = await dbSession.execute(insert(SysTenant).values(**insert_data))
    # entity.tenant_id = result.lastrowid
    return int(result.rowcount)


async def update_tenant(dbSession: AsyncSession, entity: SysTenantReq) -> int:
    update_data = SysTenant(**entity.model_dump()).model_dump(exclude_unset=True, exclude_none=True)
    stmt = update(SysTenant).values(**update_data).where(col(SysTenant.tenant_id).__eq__(entity.tenant_id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_tenant(dbSession: AsyncSession, id: int) -> int:
    # stmt = update(SysTenant).values({"del_flag": True}).where(col(SysTenant.tenant_id).__eq__(id))
    stmt = delete(SysTenant).where(col(SysTenant.tenant_id).__eq__(id))
    result: CursorResult = await dbSession.execute(stmt)
    return int(result.rowcount)


async def delete_tenants(dbSession: AsyncSession, ids: list[int]) -> int:
    # stmt = update(SysTenant).values({"del_flag": True}).where(col(SysTenant.tenant_id).in_(ids))
    stmt = delete(SysTenant).where(col(SysTenant.tenant_id).in_(ids))
    result = await dbSession.execute(stmt)
    return int(result.rowcount)
